
class InsdbController < ApplicationController

	
  def load_into_database

    @errors = []


#Start date
     s_sdate = params[:sdate].to_s
     
     i_syear = s_sdate.length - 4
     i_sday = s_sdate.length - 8 - 2

     
          s_syear = s_sdate[i_syear,4]
          s_sday = s_sdate[i_sday,2] 
          s_smonth = s_sdate[5,2]   
          
          
      if /\d\d/.match(s_sday)
        s_sday_val = s_sday
      else
        s_sday_val = s_sday[1,1]
      end       
      
      
      if /\d\d/.match(s_smonth)
        s_smonth_val = s_smonth
      else
        s_smonth_val = s_smonth[0,1]
      end    
      
      s_sdate_val = s_syear + "-" + s_smonth_val + "-" + s_sday_val
      
#End date
     s_edate = params[:edate].to_s
     
     i_eyear = s_edate.length - 4
     i_eday = s_edate.length - 8 - 2

     
          s_eyear = s_edate[i_eyear,4]
          s_eday = s_edate[i_eday,2] 
          s_emonth = s_edate[5,2]   
          
          
      if /\d\d/.match(s_eday)
        s_eday_val = s_eday
      else
        s_eday_val = s_eday[1,1]
      end       
      
      
      if /\d\d/.match(s_emonth)
        s_emonth_val = s_emonth
      else
        s_emonth_val = s_emonth[0,1]
      end    
      
      s_edate_val = s_eyear + "-" + s_emonth_val + "-" + s_eday_val   



 
        
#Bankrec (Bank Statement)start
       Acbbankrec.find_by_sql(["SELECT bank_curr_amt, description, voucher_date FROM acbbankrec WHERE client='EH' AND account='B5401' AND type='S' AND bank_short='EHDC' AND voucher_date >= ? AND voucher_date <= ? " ,s_sdate_val,s_edate_val]).each do |acbbankrec| 
       
             str_desc = parse_descf(acbbankrec.description) 
             str_desc_hb = parse_descf_hb(acbbankrec.description)
             str_desc_ed = parse_descf_ed(acbbankrec.description)
             str_desc_en = parse_descf_en(acbbankrec.description)

               str_act_ref = " "
               str_modu = " " 
               str_invnum = " "  

# Cash posting are receipts
if acbbankrec.bank_curr_amt.to_f > 0

  if str_desc != " "
             case str_desc[0,2]
              when "11"  
               str_act_ref = str_desc
               str_modu = "N"
               str_invnum = " "
              when "71"
               str_act_ref = str_desc
               str_modu = "C"
               str_invnum = " "               
              when "81"
               str_act_ref = " "
               str_modu = "S"
               str_invnum = str_desc.gsub(/810/, '910')              
              else
               str_act_ref = " "
               str_modu = " " 
               str_invnum = " "                             
             end  
               
 end   

  if str_desc_hb != " "
             str_act_ref = " "
             str_modu = "S"
             str_invnum = str_desc_hb.gsub(/810|910/, '91000')
		end
           
if str_desc_ed != " "
             case str_desc_ed[0,2]
              when "11"  
               str_act_ref = str_desc_ed[0,10]
               str_modu = "N"
               str_invnum = " "
              when "71"
               str_act_ref = str_desc_ed[0,10]
               str_modu = "C"
               str_invnum = " "               
              when "81"
               str_act_ref = " "
               str_modu = "S"
               str_invnum = str_desc_ed[0,10].gsub(/810/, '910')              
              else
               str_act_ref = " "
               str_modu = " " 
               str_invnum = " "                             
             end  
              
     end  


if str_desc_en != " "
             case str_desc_en[0,2]
              when "11"  
               str_act_ref = str_desc_en.gsub(/110/,'1100')
               str_modu = "N"
               str_invnum = " "
              when "71"
               str_act_ref = str_desc_en.gsub(/710/,'7100')
               str_modu = "C"
               str_invnum = " "               
              when "81"
               str_act_ref = " "
               str_modu = "S"
               str_invnum = str_desc_en.gsub(/810/, '9100')              
              else
               str_act_ref = " "
               str_modu = " " 
               str_invnum = " "                             
             end  
              
     end  

   end


# use the code below to reverse the sign on bank statement items
new_amount = -acbbankrec.bank_curr_amt.to_f

		         sqlinfobif = Sqlinfobif.new(:org_code=> "01",:act_ref=> str_act_ref,:financial_year=> params[:arg1],:payment_dt=> acbbankrec.voucher_date,:user_ref=> " ",:description=> "Bank:   "+acbbankrec.description,:source=> "BANK NW",:amount=> new_amount,:recovery_ctl_yr=> " ",:recovery_clt_no=> " ",:place_ref=> " ",:module=> str_modu, :invoice_number=> str_invnum) 
		         sqlinfobif.save 

       end

#Bankrec (Bank Statement)end

       
#B5404 start

Agltransact.find_by_sql(["SELECT cur_amount, description, voucher_date FROM Agltransact WHERE (account='B5404') AND (client='EH') AND (voucher_type='BS                       ') AND voucher_date >= ? AND voucher_date <= ? ",s_sdate_val,s_edate_val]).each do |agltransact| 
       
             str_desc = parse_descf(agltransact.description) 
             str_desc_hb = parse_descf_hb(agltransact.description)
             str_desc_ed = parse_descf_ed(agltransact.description)
             str_desc_en = parse_descf_en(agltransact.description)

               str_act_ref = " "
               str_modu = " " 
               str_invnum = " " 

  if str_desc != " "
             case str_desc[0,2]
              when "11"  
               str_act_ref = str_desc
               str_modu = "N"
               str_invnum = " "
              when "71"
               str_act_ref = str_desc
               str_modu = "C"
               str_invnum = " "               
              when "81"
               str_act_ref = " "
               str_modu = "S"
               str_invnum = str_desc.gsub(/810/, '910')              
              else
               str_act_ref = " "
               str_modu = " " 
               str_invnum = " "                             
             end 

     end   

  if str_desc_hb != " "
             str_act_ref = " "
             str_modu = "S"
             str_invnum = str_desc_hb.gsub(/810|910/, '91000')
		end
         

if str_desc_ed != " "
             case str_desc_ed[0,2]
              when "11"  
               str_act_ref = str_desc_ed[0,10]
               str_modu = "N"
               str_invnum = " "
              when "71"
               str_act_ref = str_desc_ed[0,10]
               str_modu = "C"
               str_invnum = " "               
              when "81"
               str_act_ref = " "
               str_modu = "S"
               str_invnum = str_desc_ed[0,10].gsub(/810/, '910')              
              else
               str_act_ref = " "
               str_modu = " " 
               str_invnum = " "                             
             end  
              
     end  

if str_desc_en != " "
             case str_desc_en[0,2]
              when "11"  
               str_act_ref = str_desc_en.gsub(/110/,'1100')
               str_modu = "N"
               str_invnum = " "
              when "71"
               str_act_ref = str_desc_en.gsub(/710/,'7100')
               str_modu = "C"
               str_invnum = " "               
              when "81"
               str_act_ref = " "
               str_modu = "S"
               str_invnum = str_desc_en.gsub(/810/, '9100')              
              else
               str_act_ref = " "
               str_modu = " " 
               str_invnum = " "                             
             end  
              
     end  



		         sqlinfobif = Sqlinfobif.new(:org_code=> "01",:act_ref=> str_act_ref,:financial_year=> params[:arg1],:payment_dt=> agltransact.voucher_date,:user_ref=> " ",:description=> "B5404:   "+agltransact.description,:source=> "BANK NW",:amount=> agltransact.cur_amount,:recovery_ctl_yr=> " ",:recovery_clt_no=> " ",:place_ref=> " ",:module=> str_modu, :invoice_number=> str_invnum) 
		         sqlinfobif.save 
  
       end

#B5404 end



#B6004 start

Agltransact.find_by_sql(["SELECT cur_amount, description, voucher_date FROM Agltransact WHERE (account='B6004') AND (client='EH') AND (voucher_type='BS                       ') AND voucher_date >= ? AND voucher_date <= ? ",s_sdate_val,s_edate_val]).each do |agltransact| 
       
             str_desc = parse_descf(agltransact.description) 
             str_desc_hb = parse_descf_hb(agltransact.description)
             str_desc_ed = parse_descf_ed(agltransact.description)
             str_desc_en = parse_descf_en(agltransact.description)

               str_act_ref = " "
               str_modu = " " 
               str_invnum = " " 

  if str_desc != " "
             case str_desc[0,2]
              when "11"  
               str_act_ref = str_desc
               str_modu = "N"
               str_invnum = " "
              when "71"
               str_act_ref = str_desc
               str_modu = "C"
               str_invnum = " "               
              when "81"
               str_act_ref = " "
               str_modu = "S"
               str_invnum = str_desc.gsub(/810/, '910')              
              else
               str_act_ref = " "
               str_modu = " " 
               str_invnum = " "                             
             end 

     end  

  if str_desc_hb != " "
             str_act_ref = " "
             str_modu = "S"
             str_invnum = str_desc_hb.gsub(/810|910/, '91000')
		end
          

if str_desc_ed != " "
             case str_desc_ed[0,2]
              when "11"  
               str_act_ref = str_desc_ed[0,10]
               str_modu = "N"
               str_invnum = " "
              when "71"
               str_act_ref = str_desc_ed[0,10]
               str_modu = "C"
               str_invnum = " "               
              when "81"
               str_act_ref = " "
               str_modu = "S"
               str_invnum = str_desc_ed[0,10].gsub(/810/, '910')              
              else
               str_act_ref = " "
               str_modu = " " 
               str_invnum = " "                             
             end  
              
     end  


if str_desc_en != " "
             case str_desc_en[0,2]
              when "11"  
               str_act_ref = str_desc_en.gsub(/110/,'1100')
               str_modu = "N"
               str_invnum = " "
              when "71"
               str_act_ref = str_desc_en.gsub(/710/,'7100')
               str_modu = "C"
               str_invnum = " "               
              when "81"
               str_act_ref = " "
               str_modu = "S"
               str_invnum = str_desc_en.gsub(/810/, '9100')              
              else
               str_act_ref = " "
               str_modu = " " 
               str_invnum = " "                             
             end  
              
     end  


		         sqlinfobif = Sqlinfobif.new(:org_code=> "01",:act_ref=> str_act_ref,:financial_year=> params[:arg1],:payment_dt=> agltransact.voucher_date,:user_ref=> " ",:description=> ":B6004:   "+agltransact.description,:source=> "BANK NW",:amount=> agltransact.cur_amount,:recovery_ctl_yr=> " ",:recovery_clt_no=> " ",:place_ref=> " ",:module=> str_modu, :invoice_number=> str_invnum) 
		         sqlinfobif.save 
  
       end

#B6004 end



#B6005 start

Agltransact.find_by_sql(["SELECT cur_amount, description, voucher_date FROM Agltransact WHERE (account='B6005') AND (client='EH') AND (voucher_type='BS                       ') AND voucher_date >= ? AND voucher_date <= ? ",s_sdate_val,s_edate_val]).each do |agltransact| 
       
             str_desc = parse_descf(agltransact.description) 
             str_desc_hb = parse_descf_hb(agltransact.description)
             str_desc_ed = parse_descf_ed(agltransact.description)
             str_desc_en = parse_descf_en(agltransact.description)


               str_act_ref = " "
               str_modu = " " 
               str_invnum = " " 
  
if str_desc != " "
             case str_desc[0,2]
              when "11"  
               str_act_ref = str_desc
               str_modu = "N"
               str_invnum = " "
              when "71"
               str_act_ref = str_desc
               str_modu = "C"
               str_invnum = " "               
              when "81"
               str_act_ref = " "
               str_modu = "S"
               str_invnum = str_desc.gsub(/810/, '910')              
              else
               str_act_ref = " "
               str_modu = " " 
               str_invnum = " "                             
             end  
              
     end  


  if str_desc_hb != " "
             str_act_ref = " "
             str_modu = "S"
             str_invnum = str_desc_hb.gsub(/810|910/, '91000')
		end


if str_desc_ed != " "
             case str_desc_ed[0,2]
              when "11"  
               str_act_ref = str_desc_ed[0,10]
               str_modu = "N"
               str_invnum = " "
              when "71"
               str_act_ref = str_desc_ed[0,10]
               str_modu = "C"
               str_invnum = " "               
              when "81"
               str_act_ref = " "
               str_modu = "S"
               str_invnum = str_desc_ed[0,10].gsub(/810/, '910')              
              else
               str_act_ref = " "
               str_modu = " " 
               str_invnum = " "                             
             end  
              
     end  


if str_desc_en != " "
             case str_desc_en[0,2]
              when "11"  
               str_act_ref = str_desc_en.gsub(/110/,'1100')
               str_modu = "N"
               str_invnum = " "
              when "71"
               str_act_ref = str_desc_en.gsub(/710/,'7100')
               str_modu = "C"
               str_invnum = " "               
              when "81"
               str_act_ref = " "
               str_modu = "S"
               str_invnum = str_desc_en.gsub(/810/, '9100')              
              else
               str_act_ref = " "
               str_modu = " " 
               str_invnum = " "                             
             end  
              
     end  


          
		         sqlinfobif = Sqlinfobif.new(:org_code=> "01",:act_ref=> str_act_ref,:financial_year=> params[:arg1],:payment_dt=> agltransact.voucher_date,:user_ref=> " ",:description=> "B6005:   "+agltransact.description,:source=> "BANK NW",:amount=> agltransact.cur_amount,:recovery_ctl_yr=> " ",:recovery_clt_no=> " ",:place_ref=> " ",:module=> str_modu, :invoice_number=> str_invnum) 
		         sqlinfobif.save 
  
       end

#B6005 end

        redirect_to :action => :index,:controller=>'sqlbifinfo'
  
    
  end
  

#start of original test
  def parse_descf( in_descr )
    i_len = in_descr.length
    
    i_for = 0
    
    out_descr = " "
    
    while (i_for + 10 <= i_len)
     s_extract = in_descr[i_for,10]
     
     if i_for+10 < i_len
		   if /11\d\d\d\d\d\d\d\d/.match(s_extract) || /71\d\d\d\d\d\d\d\d/.match(s_extract) || /81\d\d\d\d\d\d\d\d/.match(s_extract)     
	       if /\D/.match(in_descr[i_for+10,1])
	     
		       out_descr = s_extract
		       break
		     end
		     
		   end
		 else
		  if /11\d\d\d\d\d\d\d\d/.match(s_extract) || /71\d\d\d\d\d\d\d\d/.match(s_extract) || /81\d\d\d\d\d\d\d\d/.match(s_extract)
		   out_descr = s_extract
		  end
		 end
     
      i_for = i_for + 1
    end
    return out_descr
  end
#  end of original

# start of hb test
  def parse_descf_hb( in_descr )
    i_len = in_descr.length
    
    i_for = 0
    
    out_descr = " "
    
    while (i_for + 8 <= i_len)
     s_extract = in_descr[i_for,10]
     
     if i_for+8 < i_len
		   if /81\d\d\d\d\d\d/.match(s_extract) || /91\d\d\d\d\d\d/.match(s_extract)   
	       if /\D/.match(in_descr[i_for+8,1])
	     
		       out_descr = s_extract
		       break
		     end
		     
		   end
		 else
		  if /81\d\d\d\d\d\d/.match(s_extract) || /91\d\d\d\d\d\d/.match(s_extract)   
		   out_descr = s_extract
		  end
		 end
     
      i_for = i_for + 1
    end
    return out_descr
  end
  # end of test


  #start of 11 digit test
  def parse_descf_ed( in_descr )
    i_len = in_descr.length
    
    i_for = 0
    
    out_descr = " "
    
    while (i_for + 11 <= i_len)
     s_extract = in_descr[i_for,11]
     
     if i_for+10 < i_len
		   if /11\d\d\d\d\d\d\d\d\d/.match(s_extract) || /71\d\d\d\d\d\d\d\d\d/.match(s_extract) || /81\d\d\d\d\d\d\d\d\d/.match(s_extract)     
	       if /\D/.match(in_descr[i_for+11,1])
	     
		       out_descr = s_extract
		       break
		     end
		     
		   end
		 else
		  if /11\d\d\d\d\d\d\d\d\d/.match(s_extract) || /71\d\d\d\d\d\d\d\d\d/.match(s_extract) || /81\d\d\d\d\d\d\d\d\d/.match(s_extract)
		   out_descr = s_extract
		  end
		 end
     
      i_for = i_for + 1
    end
    return out_descr
  end
#  end of 11 digit test



#start of 9 digit test
  def parse_descf_en( in_descr )
    i_len = in_descr.length
    
    i_for = 0
    
    out_descr = " "
    
    while (i_for + 9 <= i_len)
     s_extract = in_descr[i_for,9]
     
     if i_for+10 < i_len
		   if /11\d\d\d\d\d\d\d/.match(s_extract) || /71\d\d\d\d\d\d\d/.match(s_extract) || /81\d\d\d\d\d\d\d/.match(s_extract)     
	       if /\D/.match(in_descr[i_for+9,1])
	     
		       out_descr = s_extract
		       break
		     end
		     
		   end
		 else
		  if /11\d\d\d\d\d\d\d/.match(s_extract) || /71\d\d\d\d\d\d\d/.match(s_extract) || /81\d\d\d\d\d\d\d/.match(s_extract)
		   out_descr = s_extract
		  end
		 end
     
      i_for = i_for + 1
    end
    return out_descr
  end
#  end of 9 digit test



  def output_bif
      bifstr=String.new

    Sqlinfobif.find(:all).each do |sqlinfobif|  
	    st_dt =  String.new 
	    t_dt  =  String.new  
	        
	    t_dt << sqlinfobif.payment_dt.to_s
	    
	    if !t_dt.empty? 
		    at_dt = t_dt.split("-")
		    st_dt << at_dt[2]+"/"+at_dt[1]+"/"+at_dt[0]
	    else
		    st_dt << " "
	    end     
	    if sqlinfobif.module != " "
         d_amount = -sqlinfobif.amount 	
         bifstr << sqlinfobif.org_code.ljust(2," ")+"\t"+sqlinfobif.act_ref.ljust(10," ")+"\t"+sqlinfobif.financial_year.ljust(4," ")+"\t"+st_dt+"\t"+sqlinfobif.user_ref.ljust(20," ")+"\t"+sqlinfobif.source.ljust(8," ")+"\t"+"      "+"\t"+d_amount.to_s.rjust(20," ")+"\t"+sqlinfobif.recovery_ctl_yr.ljust(10," ")+"\t"+sqlinfobif.recovery_clt_no.ljust(10," ")+"\t"+sqlinfobif.place_ref.ljust(20," ")+"\t"+sqlinfobif.module.ljust(1," ")+"\t"+sqlinfobif.invoice_number.ljust(10," ")+"\n"  
      end
    end
    
    File.open("C:/tabdelcashp.dat", "w")  { |f| f.write(bifstr) }

    @kn_content = bifstr 
    @kn_content_type='text/plain'
    @response.headers["Content-Type"] = @kn_content_type
    render :layout=>false

  end 
  



     
end
